﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using NTQOnlineExam.Services.Infrastructure;
using NTQOnlineExam.Services.Models;

namespace NTQOnlineExam.Services.ReportService.Term
{
    public class GetTermDTOByYearDbCommand : ICommand<SearchResult<TermDTO>>
    {
        private readonly int _pageSize;
        private readonly int _pageIndex;
        private readonly int? _year;

        private const string _sql = @"
              WITH PAGING AS (   
                     SELECT t.TermId, t.TermName, t.Description, t.Price, t.YearId, row_number() over (order by {0}) as rownumber
                     FROM Term t
                     where IsPublished = 1 AND (@YearId IS NULL OR YearId = @YearId)
                
                )
        ";

        public GetTermDTOByYearDbCommand(int pageSize, int pageIndex, int? year)
        {
            _pageSize = pageSize;
            _pageIndex = pageIndex;
            _year = year;
        }

        public SearchResult<TermDTO> Execute()
        {
            var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                IList<TermDTO> items = new List<TermDTO>();
                int totalCount = 0;
                SqlCommand cmd = new SqlCommand(BuildQuery(), connection);
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = _pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = _pageSize;
                cmd.Parameters.Add("@YearId", SqlDbType.Int).Value = _year.HasValue ? (object)_year.Value : DBNull.Value;
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                try
                {
                    if (!reader.Read()) return null;
                    totalCount = reader.GetInt32(0);
                    reader.NextResult();

                    while (reader.Read())
                    {
                        items.Add(GetDto(reader));
                    }
                }
                finally
                {
                    reader.Close();
                }
                return new SearchResult<TermDTO>(items, totalCount);
            }
        }
        private TermDTO GetDto(SqlDataReader reader)
        {
            return new TermDTO
            {
                TermId = (int)reader["TermId"],
                TermName = reader["TermName"].ToString(),
                Description = reader["Description"] != DBNull.Value ? reader["Description"].ToString() : string.Empty,
                Price = reader["Price"] != DBNull.Value ? (decimal.Parse(reader["Price"].ToString())) : (decimal?)null,
                YearId = reader["YearId"] != DBNull.Value ? (int)reader["YearId"] : 0
            };
        }

        private string BuildQuery()
        {
            var query = new StringBuilder();
            var sqlQuery = string.Format(_sql, BuildOrderClause());
            query.AppendFormat("{0} {1};{0} {2}", sqlQuery, @"SELECT COUNT(1) FROM paging", @" SELECT * FROM paging WHERE (@PageSize=0 or rownumber between @PageSize*@PageIndex+1 and @PageSize*(@PageIndex+1)) ORDER BY rownumber");
            return query.ToString();
        }

        private static string BuildOrderClause()
        {
            StringBuilder orderClause = new StringBuilder();

            orderClause.Append("t.TermId ASC");

            return orderClause.ToString();
        }
    }
}